# !/usr/bin/env python
# -*-coding:utf-8 -*-

"""
# Author     ：skyTree
# version    ：python 3.11
# Description：导出excel文件工具
"""
from django.http import FileResponse
from openpyxl.styles import Alignment, Font, Border, Side
from openpyxl.workbook import Workbook


def export_excel(products):
    wb = Workbook()
    ws = wb.active

    ws.sheet_properties.tabColor = "1072BA"
    # 设置表头样式
    header = ws.cell(row=1, column=1)
    header.value = '用户表'
    header.alignment = Alignment(horizontal='center', vertical='center')
    header.font = Font(bold=True, color='FF0000')
    ws.merge_cells(start_row=1, start_column=1, end_row=1, end_column=3)

    # 设置列宽
    ws.column_dimensions['A'].width = 20  # 设置第一列的宽度为20
    ws.column_dimensions['C'].width = 20  # 设置第一列的宽度为20
    # 设置列宽
    ws.row_dimensions[1].height = 50  # 设置第一行的高度为20

    # 写入数据
    ws.append(["姓名", "昵称", "手机号码"])
    for product in products:
        ws.append([product["username"], product["nick_name"], product["mobile"]])

    # 添加边框
    for row in ws.iter_rows(min_row=1, max_row=ws.max_row, min_col=1, max_col=3):
        for cell in row:
            cell.border = Border(left=Side(style='thin'), right=Side(style='thin'), top=Side(style='thin'),
                                 bottom=Side(style='thin'))

    # 遍历所有列，设置列的对其方式
    for col in ws.columns:
        for cell in col:
            # 设置单元格的对齐方式
            cell.style = 'Pandas'
            cell.alignment = Alignment(horizontal='center', vertical='center')

    # 遍历所有行，包括空行，从第二行开始，因为第一行是标题
    for row_idx in range(2, ws.max_row + 1):
        # 设置行高
        ws.row_dimensions[row_idx].height = 30  # 设置行高为50

    file_path = "products.xlsx"
    wb.save(file_path)
    response = FileResponse(open(file_path, 'rb'), as_attachment=True)
    return response


